﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using Scheduling.Models.Interfaces;
using Scheduling.Models.Helpers;
using Scheduling.Models.Extensions;
using Scheduling.Models.Repos.Database.Objects;

namespace Scheduling.Models.Repos.Database
{
    public class DatabaseCourseLoader : ICourseLoader
    {
        public List<Course> GetCourses()
        {
            var db = DBContext.Create();
            var q = from c in db.Courses
                    join cu in db.Users on c.AdministratorID equals cu.ID into outer1 from cu in outer1.DefaultIfEmpty()
                    join cu2 in db.Users on c.CounselorID equals cu2.ID into outer2 from cu2 in outer2.DefaultIfEmpty()
                    join d in db.Departments on c.DepartmentID equals d.ID into outer3 from d in outer3.DefaultIfEmpty()
                    select new { c, cu, cu2, d };
            return LinqHelper.GenericMap<Course>(q, "c", "cu>Administrator", "cu2>Counselor", "d>Department");
        }

        public bool CourseExists(string CourseID)
        {
            var db = DBContext.Create();
            return (from c in db.Courses where c.ID == CourseID select c).Count() > 0;
        }


        public List<Course> SearchCoursesByPatten(string Pattern)
        {
            var db = DBContext.Create();
            var q = from c in db.Courses
                    join cu in db.Users on c.AdministratorID equals cu.ID into outer1 from cu in outer1.DefaultIfEmpty()
                    join cu2 in db.Users on c.CounselorID equals cu2.ID into outer2 from cu2 in outer2.DefaultIfEmpty()
                    join d in db.Departments on c.DepartmentID equals d.ID into outer3 from d in outer3.DefaultIfEmpty()
                    where c.ID.Contains(Pattern) || c.Name.Contains(Pattern)
                    select new { c, cu, cu2, d };
            return LinqHelper.GenericMap<Course>(q, "c", "cu>Administrator", "cu2>Counselor", "d>Department");
        }

        public void InsertCourse(Course course)
        {
            var db = DBContext.Create();
            db.ExecuteCommand(
                string.Format("INSERT INTO Bookings(ID, Name, DepartmentID, Points, AdministratorID,CounselorID,SubjectID) VALUES('{0}', '{1}', {2}, {3}, '{4}', '{5}', '{6}')",
                    course.ID, course.Name, course.Department.ID, course.Points.ToString().Replace(',', '.'), course.Administrator.ID, course.Counselor.ID, course.Subject.ID
                ));
        }

        public void DeleteCourse(string CourseID)
        {
            var db = DBContext.Create();
            var q = from c in db.Courses select c;
            if (q.Count() > 0)
                db.ExecuteCommand("DELETE FROM Courses WHERE ID = {0}", CourseID);
        }


        public void EditCourse(Course course)
        {
            DeleteCourse(course.ID);
            InsertCourse(course);
        }


        public Course GetCourseByID(string ID)
        {
            var db = DBContext.Create();
            var q = from c in db.Courses
                    join cu in db.Users on c.AdministratorID equals cu.ID into o1 from cu in o1.DefaultIfEmpty()
                    join cu2 in db.Users on c.CounselorID equals cu2.ID into o2 from cu2 in o2.DefaultIfEmpty()
                    join d in db.Departments on c.DepartmentID equals d.ID into o3 from d in o3.DefaultIfEmpty()
                    join s in db.Subjects on c.SubjectID equals s.ID into o4 from s in o4.DefaultIfEmpty()
                    where c.ID.ToLower() == ID.ToLower()
                    select new { c, cu, cu2, d, s };
            if(q.Count() > 0)
                return LinqHelper.GenericMap<Course>(q, "c", "cu>Administrator", "cu2>Counselor", "d>Department", "s>Subject").First();
            else
                return null;
        }

        public void Clear()
        {
            throw new NotImplementedException();
        }
    }
}